﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Web;

/// <summary>
/// courseDao 的摘要说明
/// </summary>
public class CourseDao
{
    //获取数据库连接串
    private static string connStr = System.Configuration.ConfigurationManager.ConnectionStrings["MySQLConnectionString"].ConnectionString;

    /// <summary>
    /// 
    ///获取Info表中所有的记录
    /// </summary>
    /// <returns></returns>
    public static List<CourseInfo> FindAll(int start, int num)
    {
        List<CourseInfo> data = new List<CourseInfo>();
        MySqlConnection conn = new MySqlConnection(connStr);
        try
        {
            conn.Open();
            string sql = "select * from tb_course limit " + start + "," + num;
            //构建一个sql的命令
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            //获取数据访问的游标
            MySqlDataReader rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                CourseInfo courseInfo = new CourseInfo();
                courseInfo.Id = Convert.ToInt64(rdr["id"]);
                courseInfo.CourseNum = Convert.ToString(rdr["course_num"]);
                courseInfo.CourseName = Convert.ToString(rdr["course_name"]);
                
                data.Add(courseInfo);
            }
            rdr.Close();
        }
        catch (MySqlException ex)
        {
            Debug.WriteLine(ex.Number + ":" + ex.Message);
            throw (ex);
        }
        finally
        {
            if (conn != null)
            {
                conn.Close();
            }
        }

        return data;
    }

    
    public static CourseInfo FindOne(long id)
    {
        CourseInfo data = new CourseInfo();

        MySqlConnection conn = new MySqlConnection(connStr);

        try
        {
            conn.Open();
            //sql语句采用占位符，更加安全，可在一定程度上防止sql注入
            string sql = "select * from tb_course where id = @id ";
            //构建一个sql的命令
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            //添加对应参数
            cmd.Parameters.AddWithValue("@id", id);

            //获取数据访问的游标
            MySqlDataReader rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                data.Id = Convert.ToInt64(rdr["id"]);
                data.CourseNum = Convert.ToString(rdr["course_num"]);
                data.CourseName = Convert.ToString(rdr["course_name"]);
            }
            rdr.Close();
        }
        catch (MySqlException ex)
        {
            Debug.WriteLine("======Find方法异常信息=======");
            Debug.WriteLine(ex.Number + ":" + ex.Message);
            Debug.WriteLine("======Find方法异常信息=======");
            throw (ex);
        }
        finally
        {
            if (conn != null)
            {
                conn.Close();
            }
        }

        return data;
    }

    //通过关键字查找
    public static List<CourseInfo> Find(string keyWord)
    {
        List<CourseInfo> data = new List<CourseInfo>();
        MySqlConnection conn = new MySqlConnection(connStr);
        try
        {
            conn.Open();
            string sql = "select * from tb_course where course_name=@course_name";
            //构建一个sql的命令
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            cmd.Parameters.AddWithValue("@course_name", keyWord);
            //获取数据访问的游标
            MySqlDataReader rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                CourseInfo courseInfo = new CourseInfo();
                courseInfo.Id = Convert.ToInt64(rdr["id"]);
                courseInfo.CourseNum = Convert.ToString(rdr["course_num"]);
                courseInfo.CourseName = Convert.ToString(rdr["course_name"]);

                data.Add(courseInfo);
            }
            rdr.Close();
        }
        catch (MySqlException ex)
        {
            Debug.WriteLine(ex.Number + ":" + ex.Message);
            throw (ex);
        }
        finally
        {
            if (conn != null)
            {
                conn.Close();
            }
        }

        return data;
    }

    /// <summary>
    /// 更新一条记录
    /// </summary>
    /// <param name="data"></param>
    public static void Update(CourseInfo data)
    {

        MySqlConnection conn = new MySqlConnection(connStr);
        try
        {
            conn.Open();
            //sql语句采用占位符，更加安全，可在一定程度上防止sql注入
            string sql = "update tb_course set course_name=@course_name,course_num=@course_num where id=@id";
            //构建一个sql的命令
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            //添加对应参数
            cmd.Parameters.AddWithValue("@course_num", data.CourseNum);
            cmd.Parameters.AddWithValue("@course_name", data.CourseName);
            cmd.Parameters.AddWithValue("@id", data.Id);
            //无返回结果集
            cmd.ExecuteNonQuery();
        }
        catch (MySqlException ex)
        {
            Debug.WriteLine("======Update方法异常信息=======");
            Debug.WriteLine(ex.Number + ":" + ex.Message);
            Debug.WriteLine("======Update方法异常信息=======");
            throw (ex);
        }
        finally
        {
            if (conn != null)
            {
                conn.Close();
            }
        }

    }

    /// <summary>
    /// 删除一条记录
    /// </summary>
    /// <param name="id"></param>
    public static void Delete(long id)
    {
        MySqlConnection conn = new MySqlConnection(connStr);
        try
        {
            conn.Open();
            //sql语句采用占位符，更加安全，可在一定程度上防止sql注入
            string sql = "delete from tb_course where id=@id";
            //构建一个sql的命令
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            //添加对应参数
            cmd.Parameters.AddWithValue("@id", id);
            //无返回结果集
            cmd.ExecuteNonQuery();
        }
        catch (MySqlException ex)
        {
            Debug.WriteLine("======Delete方法异常信息=======");
            Debug.WriteLine(ex.Number + ":" + ex.Message);
            Debug.WriteLine("======Delete方法异常信息=======");
            throw (ex);
        }
        finally
        {
            if (conn != null)
            {
                conn.Close();
            }
        }
    }

    /// <summary>
    /// 插入一条记录
    /// </summary>
    /// <param name="data"></param>
    /// <returns></returns>
    public static long Insert(CourseInfo data)
    {
        long insertId = 0;
        MySqlConnection conn = new MySqlConnection(connStr);
        try
        {
            conn.Open();
            //sql语句采用占位符，更加安全，可在一定程度上防止sql注入
            string sql = "insert into tb_course " +
                            "(course_num,course_name)" +
                            "values(@course_num,@course_name)";
            //构建一个sql的命令
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            //添加对应参数
            cmd.Parameters.AddWithValue("@course_num", data.CourseNum);
            cmd.Parameters.AddWithValue("@course_name", data.CourseName);

            //无返回结果集
            cmd.ExecuteNonQuery();
            //返回插入的记录的数据库Id
            insertId = cmd.LastInsertedId;
        }
        catch (MySqlException ex)
        {
            Debug.WriteLine("======Insert方法异常信息=======");
            Debug.WriteLine(ex.Number + ":" + ex.Message);
            Debug.WriteLine("======Insert方法异常信息=======");
            throw (ex);
        }
        finally
        {
            if (conn != null)
            {
                conn.Close();
            }
        }
        return insertId;
    }
}